package com.xianmao.repo.base;

import com.xianmao.base.utilities.CalcUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

import javax.persistence.EntityManager;
import javax.persistence.NonUniqueResultException;
import javax.persistence.Query;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Created by qingquan on 2017/2/18.
 */
public class JPAUtil {

    /**
     * 查询一个列表
     * @param jpql JP QL 语句
     * @param params 查询参数
     * @return
     */
    public static <T> List<T> findList(EntityManager em, StringBuilder jpql, Map<String, Object> params) {
        return createQuery(em,jpql, params).getResultList();
    }

    /**
     * 查询一个列表
     * @param jpql JP QL 语句
     * @param params 查询参数
     * @return
     */
    public static <T> List<T> findList(EntityManager em,StringBuilder jpql, Map<String, Object> params,
                                       Pageable pageable) {

        if(pageable !=null && pageable.getSort()!= null) {
            jpql = jpql.charAt(jpql.length()-1)==';' ? jpql.deleteCharAt(jpql.length()-1) : jpql;
            jpql.append(" order by ");
            Iterator<Sort.Order> iterator = pageable.getSort().iterator();
            while(iterator.hasNext()) {
                Sort.Order order = iterator.next();
                jpql.append(order.getProperty() + " " + order.getDirection().toString() +",");
            }
            jpql.deleteCharAt(jpql.length()-1);
        }
        Query query = createQuery(em,jpql, params);
        if (pageable != null) {
            query.setFirstResult(pageable.getOffset());
            query.setMaxResults(pageable.getPageSize());

        }
        return query.getResultList();
    }

    /**
     * 使用原生sql查询目标页的数据并自动映射
     *
     * @param sql         原生sql语句
     * @param params      查询参数
     * @param pageable    分页对象
     * @param resultClass 映射的类，必须是entity包里
     * @return
     */
    @SuppressWarnings("unchecked")
    public static <T> Page<T> findPageByNativeQuery(EntityManager em, StringBuilder sql,
                                                    Map<String, Object> params, Pageable pageable, Class<T> resultClass) {
        if (resultClass == null) {
            return findPageByNativeQuery(em, sql, params, pageable);
        }
        if (pageable != null && pageable.getSort() != null) {
            sql = sql.charAt(sql.length() - 1) == ';' ? sql.deleteCharAt(sql.length() - 1) : sql;
            sql.append(" order by ");
            Iterator<Sort.Order> iterator = pageable.getSort().iterator();
            while (iterator.hasNext()) {
                Sort.Order order = iterator.next();
                sql.append(order.getProperty() + " " + order.getDirection().toString() + ",");
            }
            sql.deleteCharAt(sql.length() - 1);
        }

        Query query = createNativeQuery(em, String.valueOf(sql), params, resultClass);

        if (pageable != null) {
            query.setFirstResult(pageable.getOffset());
            query.setMaxResults(pageable.getPageSize());
        }
        List<T> list = (List<T>) query.getResultList();
        long count = getCountByNativeQuery(em, String.valueOf(sql), params);

        return new PageImpl<>(list, pageable, count);
    }


    /**
     *
     * @param jpql JP QL 语句
     * @param params 查询参数
     * @return
     */
    public static Object findSingleResult(EntityManager em,StringBuilder jpql, Map<String, Object> params) {
        Query query = createNativeQuery(em,jpql.toString(), params);
        return query.getSingleResult();
    }

    public static <T> List<T> findList(EntityManager em,StringBuilder jpql, Map<String,Object> params, int maxResult){
        return createQuery(em,jpql, params, maxResult).getResultList();
    }


    /**
     * 查询目标页的数据
     * @param jpql JP QL 语句
     * @param params 查询参数
     * @param pageable 分页对象
     * @return
     */
    public static <T> Page<T> findPage(EntityManager em, StringBuilder jpql, Map<String,Object> params, Pageable pageable){

        if(pageable !=null && pageable.getSort()!= null) {
            jpql = jpql.charAt(jpql.length()-1)==';' ? jpql.deleteCharAt(jpql.length()-1) : jpql;
            jpql.append(" order by ");
            Iterator<Sort.Order> iterator = pageable.getSort().iterator();
            while(iterator.hasNext()) {
                Sort.Order order = iterator.next();
                jpql.append(order.getProperty() + " " + order.getDirection().toString() +",");
            }
            jpql.deleteCharAt(jpql.length()-1);
        }
        Query query = createQuery(em,jpql, params);
        if (pageable != null) {
            query.setFirstResult(pageable.getOffset());
            query.setMaxResults(pageable.getPageSize());

        }

        List<T> list = query.getResultList();
        long count = getCount(em,jpql, params);

        return new PageImpl<T>(list, pageable, count);
    }

    /**
     * 获得一个对象
     * 注意，如果查询结果多于1条本方法会抛出NonUniqueResultException
     * 如果你希望最多获得1条记录并且不抛出异常，请调用getUniqueResult
     * @param jpql JP QL 语句
     * @param params 查询参数
     * @return 查询结果为0条返回null对象，查询结果为1条返回该条记录，查询结果大于1条抛出异常
     */
    public static Object getSingleResult(EntityManager em,StringBuilder jpql, Map<String,Object> params){
        List list = createQuery(em,jpql, params).getResultList();
        if(list.size() == 0){
            return null;
        }else if(list.size() == 1){
            return list.get(0);
        }else{
            throw new NonUniqueResultException( "查询结果大于1条" );
        }
    }

    /**
     * 获得一个对象
     * 本方法通过设置query.setMaxResults(1);来保证查询结果最多1条
     * @param jpql JP QL 语句
     * @param params 查询参数
     * @return 如果查询结果为0条则返回null对象，如果为1条则返回该条记录
     */
    public static <T> T getUniqueResult(EntityManager em,StringBuilder jpql, Map<String,Object> params){
        return getUniqueResult(em,String.valueOf(jpql), params);
    }

    /**
     * 获得一个对象
     * 本方法通过设置query.setMaxResults(1);来保证查询结果最多1条
     * @param jpql JP QL 语句
     * @param params 查询参数
     * @return 如果查询结果为0条则返回null对象，如果为1条则返回该条记录
     */
    public static <T> T getUniqueResult(EntityManager em,String jpql, Map<String,Object> params){
        Query query = createQuery(em,jpql, params);
        query.setMaxResults(1);
        List<T> list = query.getResultList();
        if(list.size() == 0){
            return null;
        }else{
            return list.get(0);
        }
    }

    /**
     * 获得一个对象
     * 本方法通过设置query.setMaxResults(1);来保证查询结果最多1条
     * @param jpql JP QL 语句
     * @return 如果查询结果为0条则返回null对象，如果为1条则返回该条记录
     */
    public <T> T getUniqueResult(EntityManager em,String jpql){
        return getUniqueResult(em,jpql, null);
    }

    /**
     * 获得符合条件的记录条数
     * @param jpql JP QL 语句
     * @param params 查询参数
     * @return
     */
    public static long getCount(EntityManager em,String jpql, Map<String, Object> params) {
        int fromIndex = jpql.indexOf(" from ");
        int orderIndex = jpql.indexOf(" order by ");
        if (orderIndex == -1) {
            orderIndex = jpql.length();
        }

        String countJpql = "select count(*)"
                + jpql.substring(fromIndex, orderIndex);

        if (jpql.indexOf(" group by ") != -1) {
            return createQuery(em,countJpql, params).getResultList().size();
        }

        return (Long) createQuery(em,countJpql, params).getSingleResult();
    }

    /**
     * 获得符合条件的记录条数
     * @param jpql JP QL 语句
     * @param params 查询参数
     * @return
     */
    public static long getCount(EntityManager em,StringBuilder jpql, Map<String,Object> params){
        return getCount(em,jpql.toString(), params);
    }

    /**
     * 创建查询对象
     * @param jpql JP QL 语句
     * @param params 查询参数
     * @return
     */
    public static Query createQuery(EntityManager em,String jpql, Map<String,Object> params){
        Query query = em.createQuery(jpql);
        setParameter(query, params);
        return query;
    }

    public static Query createQuery(EntityManager em,String jpql, Map<String,Object> params, int maxResult){
        Query query = em.createQuery(jpql);
        query.setMaxResults(maxResult);
        setParameter(query, params);
        return query;
    }

    /**
     * 创建查询对象
     * @param jpql JP QL 语句
     * @param params 查询参数
     * @return
     */
    public static Query createQuery(EntityManager em,StringBuilder jpql, Map<String,Object> params){
        return createQuery(em,jpql.toString(), params);
    }

    public static Query createQuery(EntityManager em,StringBuilder jpql, Map<String,Object> params, int maxResult){
        return createQuery(em,jpql.toString(), params, maxResult);
    }

    /**
     * 使用原生sql查询目标页的数据
     * @param sql 原生sql语句
     * @param params 查询参数
     * @param pageable 分页对象
     * @return
     */
    public static <T> Page<T> findPageByNativeQuery(EntityManager em,StringBuilder sql,
                                                    Map<String, Object> params, Pageable pageable) {

        if(pageable !=null && pageable.getSort()!= null) {
            sql = sql.charAt(sql.length()-1)==';' ? sql.deleteCharAt(sql.length()-1) : sql;
            sql.append(" order by ");
            Iterator<Sort.Order> iterator = pageable.getSort().iterator();
            while(iterator.hasNext()) {
                Sort.Order order = iterator.next();
                sql.append(order.getProperty() + " " + order.getDirection().toString() +",");
            }
            sql.deleteCharAt(sql.length()-1);
        }

        Query query = createNativeQuery(em,String.valueOf(sql), params);

        if (pageable != null) {
            query.setFirstResult(pageable.getOffset());
            query.setMaxResults(pageable.getPageSize());

        }

        List<T> list = query.getResultList();
        long count = getCountByNativeQuery(em,String.valueOf(sql), params);

        return new PageImpl<T>(list, pageable, count);
    }

    /**
     * 使用原生sql查询列表
     * @param sql 原生sql语句
     * @param params 查询参数
     * @return
     */
    public static <T> List<T> findListByNativeQuery(EntityManager em,StringBuilder sql,
                                                    Map<String,Object> params) {
        Query query = createNativeQuery(em, String.valueOf(sql), params);
        List<T> list = query.getResultList();

        return list;
    }

    /**
     * 创建NativeQuery
     * @param sql
     * @param params
     * @return
     */
    public static Query createNativeQuery(EntityManager em,String sql, Map<String, Object> params) {
        Query query = em.createNativeQuery(sql);
        setParameter(query, params);
        return query;
    }

    /**
     * 创建NativeQuery
     *
     * @param sql
     * @param params
     * @return
     */
    public static Query createNativeQuery(EntityManager em, String sql, Map<String, Object> params, Class resultClass) {
        Query query = em.createNativeQuery(sql, resultClass);
        setParameter(query, params);
        return query;
    }


    /**
     * 使用原生sql查询符合条件的记录条数
     * @param sql 原生sql语句
     * @param params 查询参数
     * @return
     */
    public static long getCountByNativeQuery(EntityManager em,String sql, Map<String, Object> params) {
        int fromIndex = sql.indexOf(" from ");
//		int orderIndex = sql.indexOf(" order by ");
//		if (orderIndex == -1) {
//			orderIndex = sql.length();
//		}
//
        String countJpql = "select count(*) "
                + sql.substring(fromIndex, sql.length());

        if (sql.indexOf(" group by") != -1
                && (sql.lastIndexOf(" group by") == sql.indexOf(" group by"))) {
            return createNativeQuery(em,countJpql, params).getResultList()
                    .size();
        }

        return CalcUtils.objectToLong(createNativeQuery(em, countJpql,
                params).getSingleResult());
    }

    /**
     * 设置查询参数
     * @param query 查询对象
     * @param params 查询参数
     */
    public static void setParameter(Query query, Map<String,Object> params){
        if(params != null){
            Set<String> keys = params.keySet();
            for(String key : keys){
                query.setParameter(key, params.get(key));
            }
        }
    }

}
